package cn.brilliantfuture.commentry.card;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

import cn.brilliantfuture.commentry.util.DateTimeHelper;
import cn.brilliantfuture.commentry.util.JDBCDataSource;

/**
 * 
 * Title: Communication Entry
 * 
 * Author:  LiTian  
 *
 * Date: 2012-9-24
 * 
 * Description:
 * 
 */
public class RechargeCardExpireValidJob implements Job{
	
	private static Logger logger = Logger.getLogger(RechargeCardExpireValidJob.class);

	public void execute(JobExecutionContext jobec) throws JobExecutionException {
		logger.info("Valid recharge card is expire!");
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try {
			List<Integer> list = new ArrayList<Integer>();
			conn = JDBCDataSource.getConnection();
			
			conn.setAutoCommit(false);
			
			pstm = conn.prepareStatement("select recharge_card_id from cabinet.recharge_card where DATE_FORMAT(expire_time, '%Y-%m-%d') < '" + DateTimeHelper.dateTimeToStr(new Date(), DateTimeHelper.DEFAULT_DATE_FORMAT) + "'");
			rs = pstm.executeQuery();
			while(rs.next()){
				list.add(rs.getInt("recharge_card_id"));
			}
			
			if (list.size() > 0){
				pstm = null;
				
				String sql = "update cabinet.recharge_card set card_state = 4 where recharge_card_id = ?";
				pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
				
				for (Integer id : list) {
					pstm.setInt(1, id);
					pstm.addBatch();
				}
				
				pstm.executeBatch();
				
				pstm = null;
				
				sql = "insert into recharge_card_state_record(recharge_card_id, card_state, person, insert_time) values(?, 4, '系统', now())";
				pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
				
				for (Integer id : list) {
					pstm.setInt(1, id);
					pstm.addBatch();
				}
				
				pstm.executeBatch();
			}

			conn.commit();
			conn.setAutoCommit(true);
		
		} catch (Exception e) {
			logger.error("Modfiy expire card info and record fail!\n" + e.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
	}

}
